import os
import pandas as pd
from flask import Flask, request, jsonify
import numpy as np
import numpy_financial as npf
from sqlalchemy.dialects.mysql import VARCHAR, DECIMAL
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, text
from sqlalchemy.dialects.mysql import DOUBLE
from sqlalchemy.exc import SQLAlchemyError
import uuid
import decimal
decimal.getcontext().prec = 10  # 设置高精度
decimal.getcontext().rounding = decimal.ROUND_HALF_UP  # 使用与Excel一致的四舍五入规则
username = 'root'
password = '123456'
host = '192.168.0.189'
port = '3306'
database = 'shale-gas'
engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{database}')


def load_gas_production_and_investment_data():
    """
    从数据库加载气井各年份产量数据和建设期投资，并转换为亿方单位（除以 10000）。
    建设期的建设投资使用 total_cost 值，其余年份设定为 0。
    如果未找到数据或出现数据库错误，将返回包含 None 的默认列表和 0 的建设投资数据。
    """
    query = """
        SELECT 
            gas_production_year1, gas_production_year2, gas_production_year3, gas_production_year4, gas_production_year5,
            gas_production_year6, gas_production_year7, gas_production_year8, gas_production_year9, gas_production_year10,
            gas_production_year11, gas_production_year12, gas_production_year13, gas_production_year14, gas_production_year15,
            gas_production_year16, gas_production_year17, gas_production_year18, gas_production_year19, gas_production_year20,
            total_cost
        FROM gas_well_para_optimization LIMIT 1
    """

    try:
        with engine.connect() as conn:
            result = conn.execute(text(query))
            row = result.fetchone()

        if row:
            # 读取各年产量数据并转换为亿方单位（除以 10000）
            production_data = [value / 10000 if value is not None else None for value in row[:-1]]
            # 读取建设期投资
            construction_investment = row[-1] if row[-1] is not None else 0
            # 其余年份建设投资设为 0
            investment_data = [construction_investment] + [0] * 20

            return production_data, investment_data

        # 未查询到任何数据的情况
        print("No production or investment data found in gas_well_para_optimization.")
        return [None] * 20, [0] * 21

    except SQLAlchemyError as e:
        # 数据库连接或查询出现错误
        print(f"Database error while loading production and investment data: {e}")
        return [None] * 20, [0] * 21

default_data = {
    "项目指标": [
        "产量（亿方）", "商品率（%）", "商品量（亿方）", "价格（元/千方）", "补贴（元/千方）",
        "城建税、教育附加费率", "矿产资源税率（%）", "矿产资源补偿率（%）",
        "操作费（元/千方）", "管理费（元/千方）", "销售费率", "所得税率",
        "营运期投资", "流动资金", "增值税率", "增值系数",
        "安全费用系数（元/千方）", "建设投资", "财务费用（弃置成本财务费用）", "弃置费", "销售费"
    ],
    "建设期": [0.0000, 0.96, 0.00, 1170, 200, 0.12, 0, 0, 217, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 73237, 0, 0, 0],
    "第1年": [5.5149, 0.96, 5.29, 1170, 200, 0.12, 0, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 163964, 0, 0, 0],
    "第2年": [9.6081, 0.96, 9.22, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第3年": [3.6296, 0.96, 3.48, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第4年": [2.2372, 0.96, 2.15, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第5年": [1.7147, 0.96, 1.65, 1275, 0, 0.12, 0.042, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第6年": [1.4523, 0.96, 1.39, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第7年": [1.2888, 0.96, 1.24, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第8年": [1.1564, 0.96, 1.11, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第9年": [1.0492, 0.96, 1.01, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第10年": [0.9625, 0.96, 0.92, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第11年": [0.8927, 0.96, 0.86, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第12年": [0.8371, 0.96, 0.80, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第13年": [0.7935, 0.96, 0.76, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第14年": [0.7521, 0.96, 0.72, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第15年": [0.7128, 0.96, 0.68, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第16年": [0.6754, 0.96, 0.65, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第17年": [0.6399, 0.96, 0.61, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.15, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第18年": [0.6062, 0.96, 0.58, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.25, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第19年": [0.5741, 0.96, 0.55, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.25, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0],
    "第20年": [0.5437, 0.96, 0.52, 1275, 0, 0.12, 0.0532, 0, 230, 20, 0.01, 0.25, 0, 0, 0.09, 0.8, 7.5, 0, 0, 0, 0]
}

def calculate_total_npv(output_data, discount_rate=0.06):
    # 提取从“建设期”到“第20年”的现金流数据
    cash_flows = output_data.loc[output_data["项目指标"] == "所得税后净现金流", "建设期": "第20年"].values.flatten()

    # 计算 NPV，从第1期（建设期）开始
    npv = sum(cf / (1 + discount_rate) ** i for i, cf in enumerate(cash_flows, 1))

    return npv

def calculate_irr(output_data):
    # 从建设期到第20年获取“所得税后净现金流”
    cash_flows = []
    years = ["建设期"] + [f"第{i}年" for i in range(1, 21)]

    for year in years:
        # 获取每年的所得税后净现金流
        cash_flow = output_data.loc[output_data["项目指标"] == "所得税后净现金流", year].values[0]
        cash_flows.append(cash_flow)

    # 使用 numpy_financial 的 irr 函数计算内部收益率
    irr = npf.irr(cash_flows)
    return irr

def calculate_payback_period(output_data):
    # 提取累计税后净现金流量和所得税后净现金流数据
    cumulative_cash_flows = output_data.loc[output_data["项目指标"] == "累计税后净现金流量",
                                            "建设期":"第20年"].values.flatten()
    annual_cash_flows = output_data.loc[output_data["项目指标"] == "所得税后净现金流",
                                        "建设期":"第20年"].values.flatten()

    # 计算负现金流的期数
    negative_periods = sum(cf < 0 for cf in cumulative_cash_flows)

    # 如果没有正现金流转正的年份，则返回“大于20年”
    if negative_periods >= len(annual_cash_flows):
        return "大于20年"

    # 获取最后一个负的累计现金流的绝对值
    last_negative_value = abs(cumulative_cash_flows[negative_periods - 1])

    # 获取转正后的第一个正现金流的绝对值
    first_positive_value = abs(annual_cash_flows[negative_periods])

    # 计算回收期
    payback_period = negative_periods + (last_negative_value / first_positive_value)

    return payback_period

def calculate_output(df):
    try:
        years = df.columns[1:]  # 获取年份列（建设期至第20年）
        # 初始化 output_data 结构，其中第一列为项目指标，其余列为各年份
        output_data = {
            "项目指标": [
                "现金流入", "产品营业收入", "回收流动资金", "其它收入（补贴）", "现金流出", "建设投资", "经营成本费用",
                "二级单位管理费", "操作成本", "其他成本（安全生产费）", "营业税金及附加", "增值税", "城建税、教育附加费",
                "矿产资源补偿费", "矿产资源税", "利润总额（不含所得税）", "所得税", "净利润", "所得税后净现金流",
                "累计税后净现金流量", "投资折旧","销售费用"
            ]
        }

        # 为每个年份在 output_data 中创建一个空列表以存储逐年数据，并填充初始值为 None
        for year in ["建设期"] + [f"第{i}年" for i in range(1, 21)]:
            output_data[year] = [None] * len(output_data["项目指标"])  # 初始化每个指标的年度数据为 None

        cumulative_net_cash_flow = 0  # 初始化累计税后净现金流量
        total_investment = df.loc[df["项目指标"] == "建设投资", years].sum(axis=1).values[0]
        cumulative_depreciation = 0

        # 按年份填充数据
        for i, year in enumerate(years):
            # 商品量（亿方） = 产量（亿方） * 商品率（%）
            production_volume = df.loc[df["项目指标"] == "产量（亿方）", year].values[0]
            product_rate = df.loc[df["项目指标"] == "商品率（%）", year].values[0]
            product_volume = production_volume * product_rate

            # 产品营业收入 = 商品量（亿方） * 价格（元/千方） * 10
            price = df.loc[df["项目指标"] == "价格（元/千方）", year].values[0]
            product_revenue = product_volume * price * 10
            output_data[year][output_data["项目指标"].index("产品营业收入")] = product_revenue

            # 其它收入（补贴）= 商品量（亿方） * 补贴（元/千方） *10
            subsidy_price = df.loc[df["项目指标"] == "补贴（元/千方）", year].values[0]
            subsidy = subsidy_price * product_volume * 10
            output_data[year][output_data["项目指标"].index("其它收入（补贴）")] = subsidy

            # 回收流动资金
            recovered_capital = df.loc[df["项目指标"] == "回收流动资金", year].values[0] if "回收流动资金" in df["项目指标"].values else 0
            output_data[year][output_data["项目指标"].index("回收流动资金")] = recovered_capital

            # 现金流入 = 产品营业收入 + 其它收入（补贴）+ 回收流动资金
            cash_inflow = product_revenue + subsidy + recovered_capital
            output_data[year][output_data["项目指标"].index("现金流入")] = cash_inflow

            # 建设投资
            construction_investment = df.loc[df["项目指标"] == "建设投资", year].values[0]
            output_data[year][output_data["项目指标"].index("建设投资")] = construction_investment

            # 计算经营成本费用：（1）二级单位管理费、（2）销售费, （3）操作成本，（4）其他成本（安全生产费）
            # (1) 二级单位管理费 = 产量（亿方） * 管理费（元 / 千方）*10
            management_price = df.loc[df["项目指标"] == "管理费（元/千方）", year].values[0]
            management_cost = production_volume * management_price * 10
            # (2) 销售费用
            sale_cost = df.loc[df["项目指标"] == "销售费", year].values[0]
            # (3) 操作成本 = 商品量（亿方）* 操作费（元/千方）
            operating_cost = product_volume * df.loc[df["项目指标"] == "操作费（元/千方）", year].values[0] * 10
            # （4）其他成本（安全生产费）= 产量（亿方）* 安全生产系数（元/千方）
            safety_cost = df.loc[df["项目指标"] == "安全费用系数（元/千方）", year].values[0] * production_volume * 10
            total_operating_costs = management_cost + sale_cost + operating_cost + safety_cost

            output_data[year][output_data["项目指标"].index("经营成本费用")] = total_operating_costs
            output_data[year][output_data["项目指标"].index("二级单位管理费")] =  management_cost
            output_data[year][output_data["项目指标"].index("销售费用")] =  sale_cost
            output_data[year][output_data["项目指标"].index("操作成本")] =  operating_cost
            output_data[year][output_data["项目指标"].index("其他成本（安全生产费）")] =  safety_cost

            # 营业税金及附加计算：（1）增值税，（2）城建税、教育附加费，（3）矿产资源补偿费，（4）矿产资源税
            # （1）增值税 = 现金流入 * 增值税率 * 增值税系数
            value_added_tax = cash_inflow * df.loc[df["项目指标"] == "增值税率", year].values[0] * df.loc[df["项目指标"] == "增值系数", year].values[0]
            # （2）城建税、教育附加费 = 增值税 * 城建税、教育附加费率
            urban_education_surcharge = value_added_tax * df.loc[df["项目指标"] == "城建税、教育附加费率", year].values[0]
            # （3）矿产资源补偿费 = 产品营业收入 * 矿产资源补偿率（%）
            resource_compensation_fee = product_revenue * df.loc[df["项目指标"] == "矿产资源补偿率（%）", year].values[0]
            # （4）矿产资源税
            resource_tax = product_revenue * df.loc[df["项目指标"] == "矿产资源税率（%）", year].values[0]
            business_tax_surcharge = urban_education_surcharge + resource_compensation_fee + resource_tax
            output_data[year][output_data["项目指标"].index("营业税金及附加")] =  business_tax_surcharge
            output_data[year][output_data["项目指标"].index("增值税")] =  value_added_tax
            output_data[year][output_data["项目指标"].index("城建税、教育附加费")] =  urban_education_surcharge
            output_data[year][output_data["项目指标"].index("矿产资源补偿费")] =  resource_compensation_fee
            output_data[year][output_data["项目指标"].index("矿产资源税")] =  resource_tax

            # 现金流出
            # 现金流出 = 建设投资+营运期投资+经营成本费用+营业税金及附加+财务费用（弃置成本财务费用）+ 弃置费
            cash_outflow = (construction_investment + df.loc[df["项目指标"] == "营运期投资", year].values[0] + total_operating_costs
                                                                        + business_tax_surcharge + df.loc[df["项目指标"] == "财务费用（弃置成本财务费用）", year].values[0] +
                                                                         df.loc[df["项目指标"] == "弃置费", year].values[0])
            output_data[year][output_data["项目指标"].index("现金流出")] =  cash_outflow

            #total_investment = df.loc[df["项目指标"] == "建设投资", years].sum(axis=1).values[0]
            if year == '建设期':
                depreciation = 0
            else:
                # 逐年计算剩余折旧
                remaining_gas_production = sum(
                    [df.loc[df['项目指标'] == '产量（亿方）', y].values[0] for y in years[years.get_loc(year):]]
                )
                if remaining_gas_production > 0:
                    depreciation = (total_investment - cumulative_depreciation) * (
                        df.loc[df['项目指标'] == '产量（亿方）', year].values[0] / remaining_gas_production
                    )
                    cumulative_depreciation += depreciation  # 更新累计折旧
                else:
                    depreciation = 0

            output_data[year][output_data["项目指标"].index("投资折旧")] =  int(depreciation)
            # 利润总额 = 现金流入 - 经营成本费用 - 营业税金及附加 - 财务费用（弃置成本财务费用） - 弃置费 - 折旧
            profit_total = cash_inflow - ( total_operating_costs + business_tax_surcharge + depreciation + df.loc[df["项目指标"] == "财务费用（弃置成本财务费用）", year].values[0] +
                                                                         df.loc[df["项目指标"] == "弃置费", year].values[0])
            # 如果净利润小于0，所得税设为0，否则按正常计算
            if profit_total < 0:
                income_tax = 0
            else:
                # 所得税 = 利润总额 * 所得税率
                income_tax = profit_total * df.loc[df["项目指标"] == "所得税率", year].values[0]
            # 净利润 = 利润总额 - 所得税
            net_profit = profit_total - income_tax
            # 所得税后净现金流 = 现金流入 - 现金流出 - 所得税
            net_cash_flow_after_tax = cash_inflow - cash_outflow - income_tax
            # 累计税后净现金流量 = 累计税后净现金流量 + 所得税后净现金流
            cumulative_net_cash_flow += net_cash_flow_after_tax

            # 将结果添加到 output_data 中
            output_data[year][output_data["项目指标"].index("利润总额（不含所得税）")] =profit_total
            output_data[year][output_data["项目指标"].index("所得税")] =  income_tax
            output_data[year][output_data["项目指标"].index("净利润")] =  net_profit
            output_data[year][output_data["项目指标"].index("所得税后净现金流")] =  net_cash_flow_after_tax
            output_data[year][output_data["项目指标"].index("累计税后净现金流量")] =  cumulative_net_cash_flow

        # 将所有行结果合并到 DataFrame 中
        res_data =  pd.DataFrame(output_data)
        total_npv = calculate_total_npv(res_data)
        internal_rate_of_return = calculate_irr(res_data)
        payback_period = calculate_payback_period(res_data)
        return pd.DataFrame(output_data),total_npv,internal_rate_of_return,payback_period


    except Exception as e:
        raise ValueError(f"Error in calculate_output: {str(e)}")
